package service.report.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import model.TContract;
import model.app.AppFinance;
import model.app.AppInfo;

import dao.LoangrantDao;
import dao.LoangrantrecordDao;
import dao.LoaninterestDao;
import dao.LoaninterestrecordDao;
import dao.TContractDao;
import dao.app.AppFinanceDao;
import dao.app.AppInfoDAO;

import exception.Exceptions;
import service.report.ReportMoneyService;
import util.MoneyCalc;
import vo.MoneyDoubleBean;

public class ReportMoneyServiceImpl implements ReportMoneyService{
	
	private String searchDate;
	
	private TContractDao tContractDao;
	
	private LoangrantDao loangrantDao;
	
	private LoangrantrecordDao loangrantrecordDao;
	
	private LoaninterestDao loaninterestDao;
	
	private LoaninterestrecordDao loaninterestrecordDao;
	
	private AppFinanceDao appFinanceDao;
	
	private AppInfoDAO appInfoDAO;
	
	private BigDecimal allMoney;//总注册资本
	
	private BigDecimal allAssert;//总资产
	
	private BigDecimal money5;
	
	private BigDecimal money10;

	@Override
	public Map<String, BigDecimal> getListReportMoney(String searchDate)
			throws Exceptions {
		this.searchDate = searchDate;
		List<AppFinance> appFinanceList = this.appFinanceDao.findModel("DATE_FORMAT(financedate, '%Y-%m') = DATE_FORMAT('" + this.searchDate + "', '%Y-%m')");
		if(appFinanceList != null){
			allMoney = new BigDecimal(appFinanceList.get(0).getOperatregfinance());
			allAssert = new BigDecimal(appFinanceList.get(0).getAsset());
		}else{
			allMoney = new BigDecimal(100000000.00);
		}
		if(allMoney.compareTo(new BigDecimal(100000000)) == -1){
			this.money5 = new BigDecimal(500000);
		}else{
			this.money5 = allMoney.multiply(new BigDecimal(0.05)).setScale(2, BigDecimal.ROUND_HALF_UP);
		}
		this.money10 = allMoney.multiply(new BigDecimal(0.10)).setScale(2, BigDecimal.ROUND_HALF_UP);
		
		Map<String, BigDecimal> countMap = new HashMap<String, BigDecimal>();
		
		// 当月平均利率
		BigDecimal thisMonAvgRate = this.getAvgRate(1);
		countMap.put("thisMonAvgRate", thisMonAvgRate);

		// 累计平均利率
		BigDecimal allAvgRate = this.getAvgRate(2);
		countMap.put("allAvgRate", allAvgRate);
		
		//利息收入年
		BigDecimal rateIncomeYear = getRateIncome(1);
		countMap.put("rateIncomeYear", rateIncomeYear);
		
		//利息收入月
		BigDecimal rateIncomeMonth = getRateIncome(2);
		countMap.put("rateIncomeMonth", rateIncomeMonth);
		
		
		//当月贷款额
		BigDecimal loanOfThisMonth = getLoan(1);
		countMap.put("loanOfThisMonth", loanOfThisMonth);
		
		//当年累计贷款比数
		BigDecimal countOfThisYear = getAllRateRecordCount(3);
		countMap.put("countOfThisYear", countOfThisYear);
		
		//当年累计贷款额
		BigDecimal loanOfThisYear = getLoan(2);
		countMap.put("loanOfThisYear", loanOfThisYear);
		
		//当年 平均每笔贷款额
		BigDecimal loanOfThisYearAvg;
		try {
			loanOfThisYearAvg = loanOfThisYear.divide(countOfThisYear).setScale(2, BigDecimal.ROUND_HALF_UP);
			countMap.put("loanOfThisYearAvg", loanOfThisYearAvg);
		} catch (Exception e) {
			countMap.put("loanOfThisYearAvg", new BigDecimal(0.00));
		}
		//countMap.put("loanOfThisYearAvg", loanOfThisYearAvg);
		
		//期末贷款比数  （当月贷款比数）
		BigDecimal countOfThisMonth = getAllRateRecordCount(1);
		countMap.put("countOfThisMonth", countOfThisMonth);
		
		//期末贷款余额（当月贷款余额）
		BigDecimal balanceOfThisMonth = getBalance(1);
		countMap.put("balanceOfThisMonth", balanceOfThisMonth);
		
		//贷款余额按期限划分 0-6
		BigDecimal balanceByDateLine06 = getBalanceByDateLine(1);
		countMap.put("balanceByDateLine06", balanceByDateLine06);
		
		//贷款余额按期限划分 7-12
		BigDecimal balanceByDateLine712 = getBalanceByDateLine(2);
		countMap.put("balanceByDateLine712", balanceByDateLine712);
		
		//贷款余额按期限划分 >12
		BigDecimal balanceByDateLine12 = getBalanceByDateLine(3);
		countMap.put("balanceByDateLine12", balanceByDateLine12);
		
		//贷款余额按照资金投向划分 涉农
		BigDecimal balanceByObjectN = getBalanceByObject(1);
		countMap.put("balanceByObjectN", balanceByObjectN);
		
		//贷款余额按照资金投向划分 小企业
		BigDecimal balanceByObjectE = getBalanceByObject(2);
		countMap.put("balanceByObjectE", balanceByObjectE);
		
		//贷款余额按照资金投向划分 其他
		BigDecimal balanceByObjectO = getBalanceByObject(3);
		countMap.put("balanceByObjectO", balanceByObjectO);
		
		//贷款不超过%5余额
		BigDecimal balanceByMoney5 = getBalanceByMoneyType(1, 1);
		countMap.put("balanceByMoney5", balanceByMoney5);
		
		//贷款不超过%5所占比例
		
		try {
			BigDecimal balanceByMoneyRate5 = balanceByMoney5.divide(this.allAssert).setScale(2, BigDecimal.ROUND_HALF_UP);
			countMap.put("balanceByMoneyRate5", balanceByMoneyRate5);
		} catch (Exception e) {
			countMap.put("balanceByMoneyRate5", new BigDecimal(0.00));
		}
		
		
		//贷款%5-10%余额
		BigDecimal balanceByMoney10 = getBalanceByMoneyType(2, 1);
		countMap.put("balanceByMoney10", balanceByMoney10);
		
		//贷款5%-10%所占比例
		try {
			BigDecimal balanceByMoneyRate10 = balanceByMoney10.divide(this.allAssert).setScale(2, BigDecimal.ROUND_HALF_UP);
			countMap.put("balanceByMoneyRate10", balanceByMoneyRate10);
		} catch (Exception e) {
			countMap.put("balanceByMoneyRate10", new BigDecimal(0.00));
		}
		
		
		//贷款>10%余额
		BigDecimal balanceByMoney11 = getBalanceByMoneyType(3, 1);
		countMap.put("balanceByMoney11", balanceByMoney11);
		
		//贷款>10%所占比例
		try {
			BigDecimal balanceByMoneyRate11 = balanceByMoney11.divide(this.allAssert).setScale(2, BigDecimal.ROUND_HALF_UP);
			countMap.put("balanceByMoneyRate11", balanceByMoneyRate11);
		} catch (Exception e) {
			countMap.put("balanceByMoneyRate11", new BigDecimal(0.00));
		}
		
		return countMap;
	}
	
	/**
	 * type = 1 当月平均利率
	 * type = 2 累计平均利率
	 * */
	protected BigDecimal getAvgRate(int type){
		BigDecimal allRate = new BigDecimal(0);
		BigDecimal allCount = new BigDecimal(0);
			allRate = this.getAllRateCount(type);
			allCount = this.getAllRateRecordCount(type);
		try {
			return allRate.divide(allCount).setScale(5,BigDecimal.ROUND_HALF_UP);
		} catch (Exception e) {
			return new BigDecimal(0.00).setScale(5, BigDecimal.ROUND_HALF_UP);
		}
	}
	
	// 贷款利率总计
	@SuppressWarnings({ "unchecked", "rawtypes" })
	protected BigDecimal getAllRateCount(int type){
		List list = this.getListBean(type);
		if(list == null){
			return new BigDecimal(0.00);
		}
		return MoneyCalc.moneyTotal(list).setScale(2, BigDecimal.ROUND_HALF_UP);
	}
	
	// 贷款比数总计
	@SuppressWarnings({ "rawtypes"})
	protected BigDecimal getAllRateRecordCount(int type){
		List list = this.getListBean(type);
		if(list == null){
			return new BigDecimal(0.00);
		}
		return new BigDecimal(list.size());
	}
	
	/**
	 * type = 1 当月利率
	 * type = 2 累计利率
	 * type = 3 当年利率
	 * */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	private List getListBean(int type){
		List<MoneyDoubleBean> list = new ArrayList<MoneyDoubleBean>();
		String select = "DISTINCT l.contractid";
		String from = "";
		if(type == 1){
			from = "Loangrant as l where DATE_FORMAT(l.grantdate, '%Y-%m') = DATE_FORMAT('" + this.searchDate + "', '%Y-%m') and (l.state = 2 or l.state = 3 or l.state = 0)";
		}else if(type == 2){
			from = "Loangrant as l where (l.state = 2 or l.state = 3 or l.state = 0)";
		}else if(type == 3){
			from = "Loangrant as l where DATE_FORMAT(l.grantdate, '%Y') = DATE_FORMAT('" + this.searchDate + "', '%Y') and (l.state = 2 or l.state = 3 or l.state = 0)";
		}
		
		List<?> listBean = this.loangrantDao.findSqlQuery(select, from, 0);
		
		if(listBean != null){
			for(int i = 0, j = listBean.size(); i < j; i++){
				Map<Object,Object> mapbean = (Map<Object, Object>)listBean.get(i);
				int loanid = (Integer)mapbean.get("contractid");
				TContract tContract = this.tContractDao.findByOne("Id = " + loanid + " and contractstate = 1");
				list.add(new MoneyDoubleBean(tContract.getPerformrates()));
			}
		}
		return list;
	}
	
	/**
	 * 利息收入
	 * type = 1 年
	 * type = 2月
	 * */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public BigDecimal getRateIncome(int type){
		String from = "";
		if(type == 1){
			from = "Loaninterestrecord as l where DATE_FORMAT(l.confirmdate, '%Y') = DATE_FORMAT('" + this.searchDate + "', '%Y') and l.state = 0";
		}else if(type == 2){
			from = "Loaninterestrecord as l where DATE_FORMAT(l.confirmdate, '%Y-%m') = DATE_FORMAT('" + this.searchDate + "', '%Y-%m') and l.state = 0";
		}
		String select = "new vo.MoneyDoubleBean(l.grantmoney)";
		//String from = "loaninterestrecord as l where DATE_FORMAT(l.confirmdate, '%Y-%m') <= DATE_FORMAT('" + this.searchDate + "', '%Y-%m') and DATE_FORMAT('" + this.searchDate + "', '%Y-%m') <= DATE_FORMAT(l.confirmdate, '%Y-%m')";
		//String from = "loaninterestrecord as l where DATE_FORMAT(l.confirmdate, '%Y') = DATE_FORMAT('" + this.searchDate + "', '%Y-%m') and l.state = 0";
		List list = this.loaninterestDao.findQuery(select, from);
		return MoneyCalc.moneyTotal(list).divide(new BigDecimal(10000)).setScale(2, BigDecimal.ROUND_HALF_UP);
	}
	
	/**
	 * 贷款额
	 * type = 1 当月
	 * type = 2 当年
	 * */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private BigDecimal getLoan(int type){
		String select = "new vo.MoneyDoubleBean(l.grantmoney)";
		String from = "";
		if(type == 1){
			from = "Loangrant as l where DATE_FORMAT(l.grantdate, '%Y-%m') = DATE_FORMAT('" + this.searchDate + "', '%Y-%m') and (l.state = 2 or l.state = 3 or l.state = 0)";
		}else if(type == 2){
			from = "Loangrant as l where DATE_FORMAT(l.grantdate, '%Y') = DATE_FORMAT('" + this.searchDate + "', '%Y') and (l.state = 2 or l.state = 3 or l.state = 0)";
		}
		
		List list = this.loaninterestDao.findQuery(select, from);
		return MoneyCalc.moneyTotal(list).divide(new BigDecimal(10000)).setScale(2, BigDecimal.ROUND_HALF_UP);
	}
	
	/**
	 * 贷款余额
	 * type = 1当月
	 * type = 2当年
	 * type = 3累计
	 * */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private BigDecimal getBalance(int type){
		String select = "new vo.MoneyDoubleBean(l.balance)";
		String from = "";
		if(type == 1){
			from = "Loangrant as l where DATE_FORMAT(l.grantdate, '%Y-%m') = DATE_FORMAT('" + this.searchDate + "', '%Y-%m') and (l.state = 2 or l.state = 3 or l.state = 0)";
		}else if(type == 2){
			from = "Loangrant as l where DATE_FORMAT(l.grantdate, '%Y') = DATE_FORMAT('" + this.searchDate + "', '%Y') and (l.state = 2 or l.state = 3 or l.state = 0)";
		}else if(type == 3){
			from = "Loangrant as l where (l.state = 2 or l.state = 3 or l.state = 0)";
		}
		
		List list = this.loaninterestDao.findQuery(select, from);
		return MoneyCalc.moneyTotal(list).divide(new BigDecimal(10000)).setScale(2, BigDecimal.ROUND_HALF_UP);
	}
	
	/**
	 * 贷款余额 按照期限划分
	 * type = 1 0-6
	 * type = 2 6-12
	 * type = 3 >12
	 * */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private BigDecimal getBalanceByDateLine(int type){
		String select = "new vo.MoneyDoubleBean(l.balance)";
		String from = "";
		if(type == 1){
			from = "Loangrant as l, TContract as c where TIMESTAMPDIFF(MONTH,c.startdate,c.enddate) <= 6 and TIMESTAMPDIFF(MONTH,c.startdate,c.enddate) >= 0 and (l.state = 0 or l.state = 2 or l.state = 3) and (c.contractstate = 1 or c.contractstate = 0) and l.contractid = c.id";
		}else if(type == 2){
			from = "Loangrant as l, TContract as c where TIMESTAMPDIFF(MONTH,c.startdate,c.enddate) <= 12 and TIMESTAMPDIFF(MONTH,c.startdate,c.enddate) >= 7 and (l.state = 0 or l.state = 2 or l.state = 3) and (c.contractstate = 1 or c.contractstate = 0) and l.contractid = c.id";
		}else if(type == 3){
			from = "Loangrant as l, TContract as c where TIMESTAMPDIFF(MONTH,c.startdate,c.enddate) > 12 and (l.state = 0 or l.state = 2 or l.state = 3) and (c.contractstate = 1 or c.contractstate = 0) and l.contractid = c.id";
		}
		
		List list = this.loaninterestDao.findQuery(select, from);
		return MoneyCalc.moneyTotal(list).divide(new BigDecimal(10000)).setScale(2, BigDecimal.ROUND_HALF_UP);
	}
	
	/**
	 * 贷款余额 按照资金投向划分
	 * type = 1 涉农
	 * type = 2 企业
	 * type = 3 其他
	 * */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private BigDecimal getBalanceByObject(int type){
		String select = "new vo.MoneyDoubleBean(l.balance)";
		String from = "";
		if(type == 1){
			from = "Loangrant as l, TContract as c where c.isaboutn = 1 and (l.state = 0 or l.state = 2 or l.state = 3) and (c.contractstate = 1 or c.contractstate = 0) and l.contractid = c.id";
		}else if(type == 2){
			from = "Loangrant as l, TContract as c where c.clienttype = 2 and (l.state = 0 or l.state = 2 or l.state = 3) and (c.contractstate = 1 or c.contractstate = 0) and l.contractid = c.id";
		}else if(type == 3){
			from = "Loangrant as l, TContract as c where (c.isaboutn <> 1 and c.clienttype <> 2) and (l.state = 0 or l.state = 2 or l.state = 3) and (c.contractstate = 1 or c.contractstate = 0) and l.contractid = c.id";
		}
		
		List list = this.loaninterestDao.findQuery(select, from);
		return MoneyCalc.moneyTotal(list).divide(new BigDecimal(10000)).setScale(2, BigDecimal.ROUND_HALF_UP);
	}
	
	/**
	 * 贷款余额 按照资金划分
	 * type = 1 <=%5
	 * type = 2 >5% & <= 10%
	 * type = 3 >10%
	 * 
	 * dType = 1 返回余额
	 * dType = 2 返回比数
	 * @throws Exceptions 
	 * */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	private BigDecimal getBalanceByMoneyType(int type, int dType) throws Exceptions{
		List list = this.getBalanceByMoney(type);
		if(dType == 1){
			if(list != null){
				return new BigDecimal(list.size());
			}else{
				return new BigDecimal(0.00);
			}
		}else if(dType == 2){
			return MoneyCalc.moneyTotal(list).divide(new BigDecimal(10000)).setScale(2, BigDecimal.ROUND_HALF_UP);
		}else{
				throw new Exceptions("error!");
		}
	}
	
	/**
	 * type = 1 <=%5
	 * type = 2 >5% & <= 10%
	 * type = 3 >10%
	 * */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private List getBalanceByMoney(int type){
		String select1 = "DISTINCT l.contractid";
		String from1 = "Loangrant as l where DATE_FORMAT(l.grantdate, '%Y-%m') = DATE_FORMAT('" + this.searchDate + "', '%Y-%m') and (l.state = 2 or l.state = 3 or l.state = 0)";
		List<?> listBean = this.tContractDao.findSqlQuery(select1, from1, 0);
		List<BigDecimal> list = new ArrayList<BigDecimal>();
		if(listBean != null){
			for(int i = 0, j = listBean.size(); i < j; i++){
				Map<Object,Object> mapbean = (Map<Object, Object>)listBean.get(i);
				int loanid = (Integer)mapbean.get("contractid");
				
				String select = "new vo.MoneyDoubleBean(l.balance)";
				String from = "Loangrant as l where (l.state = 2 or l.state = 3 or l.state = 0) and l.contractid = " + loanid;
				
				List listQ = this.loaninterestDao.findQuery(select, from);
				BigDecimal listMoney = MoneyCalc.moneyTotal(listQ).divide(new BigDecimal(10000)).setScale(2, BigDecimal.ROUND_HALF_UP);
				
				if(type == 1){
					if(listMoney.compareTo(money5) == 1){
						list.add(listMoney);
					}
				}else if(type == 2){
					if((listMoney.compareTo(money5) == 0 || listMoney.compareTo(money5) == 1) && listMoney.compareTo(money10) == -1){
						list.add(listMoney);
					}
				}else if(type == 3){
					if(listMoney.compareTo(money10) == 0 || listMoney.compareTo(money10) == 1){
						list.add(listMoney);
					}
				}
				list.add(listMoney);
			}
		}
		return list;
	}
	

	public String getSearchDate() {
		return searchDate;
	}

	public void setSearchDate(String searchDate) {
		this.searchDate = searchDate;
	}

	public LoangrantDao getLoangrantDao() {
		return loangrantDao;
	}

	public void setLoangrantDao(LoangrantDao loangrantDao) {
		this.loangrantDao = loangrantDao;
	}

	public LoangrantrecordDao getLoangrantrecordDao() {
		return loangrantrecordDao;
	}

	public void setLoangrantrecordDao(LoangrantrecordDao loangrantrecordDao) {
		this.loangrantrecordDao = loangrantrecordDao;
	}

	public LoaninterestDao getLoaninterestDao() {
		return loaninterestDao;
	}

	public void setLoaninterestDao(LoaninterestDao loaninterestDao) {
		this.loaninterestDao = loaninterestDao;
	}

	public TContractDao gettContractDao() {
		return tContractDao;
	}

	public void settContractDao(TContractDao tContractDao) {
		this.tContractDao = tContractDao;
	}

	public LoaninterestrecordDao getLoaninterestrecordDao() {
		return loaninterestrecordDao;
	}

	public void setLoaninterestrecordDao(LoaninterestrecordDao loaninterestrecordDao) {
		this.loaninterestrecordDao = loaninterestrecordDao;
	}

	public AppFinanceDao getAppFinanceDao() {
		return appFinanceDao;
	}

	public void setAppFinanceDao(AppFinanceDao appFinanceDao) {
		this.appFinanceDao = appFinanceDao;
	}

	@Override
	public AppFinance getFinance(String searchDate) throws Exceptions {
		List<AppFinance> appFinanceList = this.appFinanceDao.findModel("DATE_FORMAT(financedate, '%Y-%m') = DATE_FORMAT('" + searchDate + "', '%Y-%m')");
		if(appFinanceList != null){
			return appFinanceList.get(0);
		}else{
			return null;
		}
	}

	@Override
	public AppInfo getInfor(String object) throws Exceptions {
		try {
			return this.appInfoDAO.findOne("name", object);
		} catch (Exception e) {
			return null;
		}
	}

	public AppInfoDAO getAppInfoDAO() {
		return appInfoDAO;
	}

	public void setAppInfoDAO(AppInfoDAO appInfoDAO) {
		this.appInfoDAO = appInfoDAO;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public BigDecimal getAllIncome(String searchDate) throws Exceptions {
		String select = "new vo.MoneyDoubleBean(a.profitcurrent)";
		String from = "AppFinance as a where DATE_FORMAT(a.financedate, '%Y') = DATE_FORMAT('" + searchDate + "', '%Y')";
		
		List list = this.loaninterestDao.findQuery(select, from);
		return MoneyCalc.moneyTotal(list).divide(new BigDecimal(10000)).setScale(2, BigDecimal.ROUND_HALF_UP);
	}

}
